package com.gemcoder.system.service;

import static com.gemcoder.system.datasource.JdbcTemplateUtil.executeSQL;
import java.util.ArrayList;
import java.util.List;
import java.util.HashMap;
import java.util.Map;
import org.springframework.stereotype.Service;
import static com.gemcoder.system.service.UserSdkService.sdkOfGetCurrentUserName;
import static com.gemcoder.system.service.UserSdkService.sdkOfGetCurrentUserId;
import com.gemcoder.system.bo.Result;
import static com.gemcoder.system.service.permission.PermissionSdkService.sdkOfgetAllPermissions;
import static com.gemcoder.system.service.permission.PermissionSdkService.sdkOfgetAllMenus;
import java.sql.SQLException;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.sql.SqlExecutor;
import cn.hutool.db.sql.SqlBuilder;
import cn.hutool.db.sql.SqlUtil;
import java.time.LocalDateTime;

@Service
public class RevenuedatareportingService {

    public Result getAllProjectNames(Map<String, Object> paramMap) {
        // 构建查询所有项目名称的SQL
        StringBuilder sql = new StringBuilder("SELECT id, project_name FROM project");
        // 执行SQL查询
        List<Map<String, Object>> result = executeSQL(sql.toString());
        // 构建返回结果
        Result response = new Result();
        response.setFlag(true);
        response.setMessage("查询成功");
        response.setData(result);
        return response;
    }

    public Result getOperationalDataDetails(Map<String, Object> paramMap) {
        Integer id = paramMap.get("id") != null ? Integer.valueOf(String.valueOf(paramMap.get("id"))) : null;
        if (id == null) {
            return new Result(false, "ID不能为空", null);
        }
        // 查询运营数据详情
        StringBuilder sql = new StringBuilder("SELECT id, date, project_id, medicine_usage_id, medicine_usage_quantity, medicine_id, medicine_name, medicine_price, medicine_total_cost, drug_consumption, electricity_consumption, water_consumption, reporter, DATE_FORMAT(report_time, '%Y-%m-%d %H:%i:%s') AS report_time FROM operational_data WHERE id = ?");
        List<Map<String, Object>> operationalData = executeSQL(sql.toString(), id);
        if (operationalData.isEmpty()) {
            return new Result(false, "未找到对应的运营数据", null);
        }
        Object projectId = operationalData.get(0).get("project_id");
        // 查询药品使用信息
        StringBuilder medicineSql = new StringBuilder("SELECT id, project_id, medicine_id, unit_price, create_username, create_time, update_username, update_time FROM project_medicine WHERE project_id = ?");
        List<Map<String, Object>> medicineUsageData = executeSQL(medicineSql.toString(), projectId);
        Map<String, Object> result = new HashMap<>();
        result.put("operationalData", operationalData.get(0));
        result.put("medicineUsageData", medicineUsageData);
        return new Result(true, "查询成功", result);
    }

    public Result getDrugUsageInfo(Map<String, Object> paramMap) {
        Integer projectId = paramMap.get("projectId") != null ? Integer.valueOf(String.valueOf(paramMap.get("projectId"))) : null;
        if (projectId == null) {
            return new Result(false, "项目ID不能为空", null);
        }
        // 查询药品使用信息
        StringBuilder sql = new StringBuilder("SELECT id, project_id, medicine_id, unit_price, create_username, create_time, update_username, update_time FROM project_medicine WHERE project_id = ?");
        List<Map<String, Object>> drugUsageInfo = executeSQL(sql.toString(), projectId);
        return new Result(true, "查询成功", drugUsageInfo);
    }

    public Result getDrugData(Map<String, Object> paramMap) {
        Integer current = paramMap.get("current") != null ? Integer.valueOf(String.valueOf(paramMap.get("current"))) : 1;
        Integer pageSize = paramMap.get("pageSize") != null ? Integer.valueOf(String.valueOf(paramMap.get("pageSize"))) : 10;
        // 查询药品数据
        StringBuilder sql = new StringBuilder("SELECT id, medicine_name, unit_price, create_username, create_time, update_username, update_time FROM medicine LIMIT ?, ?");
        List<Map<String, Object>> drugData = executeSQL(sql.toString(), (current - 1) * pageSize, pageSize);
        // 查询总条数
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) AS total FROM medicine");
        List<Map<String, Object>> totalResult = executeSQL(countSql.toString());
        Integer total = Integer.valueOf(String.valueOf(totalResult.get(0).get("total")));
        Map<String, Object> result = new HashMap<>();
        result.put("data", drugData);
        result.put("total", total);
        return new Result(true, "查询成功", result);
    }

    public Result updateOperationalData(Map<String, Object> paramMap) {
        Integer id = paramMap.get("id") != null ? Integer.valueOf(String.valueOf(paramMap.get("id"))) : null;
        if (id == null) {
            return new Result(false, "ID不能为空", null);
        }
        String date = paramMap.get("date") != null ? String.valueOf(paramMap.get("date")) : null;
        String drugConsumption = paramMap.get("drugConsumption") != null&&!String.valueOf(paramMap.get("drugConsumption")).isEmpty() ? String.valueOf(paramMap.get("drugConsumption")) : null;
        String electricityConsumption = paramMap.get("electricityConsumption") != null ? String.valueOf(paramMap.get("electricityConsumption")) : null;
        String waterConsumption = paramMap.get("waterConsumption") != null ? String.valueOf(paramMap.get("waterConsumption")) : null;
        String reporter = paramMap.get("reporter") != null ? String.valueOf(paramMap.get("reporter")) : null;
        String reportTime = paramMap.get("reportTime") != null ? String.valueOf(paramMap.get("reportTime")) : null;
        // 更新运营数据
        StringBuilder sql = new StringBuilder("UPDATE operational_data SET date = ?, drug_consumption = ?, electricity_consumption = ?, water_consumption = ?, reporter = ?, report_time = ?  WHERE id = ?");
        int rows = executeSQL(sql.toString(), date, drugConsumption, electricityConsumption, waterConsumption, reporter, reportTime, id);
        if (rows > 0) {
            return new Result(true, "更新成功", null);
        } else {
            return new Result(false, "更新失败", null);
        }
    }

    public Result getOperationalData(Map<String, Object> paramMap) {
        Integer current = paramMap.get("current") != null ? Integer.valueOf(String.valueOf(paramMap.get("current"))) : 1;
        Integer pageSize = paramMap.get("pageSize") != null ? Integer.valueOf(String.valueOf(paramMap.get("pageSize"))) : 10;
        String projectName = paramMap.get("projectName") != null ? String.valueOf(paramMap.get("projectName")) : null;
        // 查询运营数据
        StringBuilder sql = new StringBuilder("SELECT \n" +
                "    id, \n" +
                "    date, \n" +
                "    project_id,\n" +
                "    (SELECT project_name FROM project WHERE id=project_id) AS projectName, \n" +
                "    drug_consumption, \n" +
                "    electricity_consumption, \n" +
                "    water_consumption, \n" +
                "    reporter, \n" +
                "    DATE_FORMAT(report_time, '%Y-%m-%d %H:%i:%s') AS report_time \n" +
                "FROM \n" +
                "    operational_data \n");
        List<Object> params = new ArrayList<>();
        List<Object> countParams = new ArrayList<>();
        StringBuilder countSql = new StringBuilder("SELECT \n" +
                "    COUNT(*) AS total \n" +
                "FROM \n" +
                "    operational_data od\n" +
                "JOIN \n" +
                "    project p ON od.project_id = p.id\n");
        if (projectName != null && !projectName.isEmpty()) {
            sql.append("WHERE \n" +
                    "    (SELECT project_name FROM project WHERE id = project_id) LIKE CONCAT('%', ?, '%')");
            countSql.append("WHERE \n" +
                    "    p.project_name LIKE CONCAT('%', ?, '%');");
            params.add(projectName);
            countParams.add(projectName);
        }
        sql.append("LIMIT ?,?");
        params.add((current - 1) * pageSize);
        params.add(pageSize);
        List<Map<String, Object>> operationalData = executeSQL(sql.toString(), params.toArray());
        // 查询总条数
        List<Map<String, Object>> totalResult = executeSQL(countSql.toString(), countParams.toArray());
        Integer total = Integer.valueOf(String.valueOf(totalResult.get(0).get("total")));
        Map<String, Object> result = new HashMap<>();
        result.put("data", operationalData);
        result.put("total", total);
        return new Result(true, "查询成功", result);
    }

    public Result getProjectDrugs(Map<String, Object> paramMap) {
        Integer projectId = paramMap.get("projectId") != null ? Integer.valueOf(String.valueOf(paramMap.get("projectId"))) : null;
        if (projectId == null) {
            return new Result(true, "查询信息为空", new HashMap<>());
        }
        Integer current = paramMap.get("current") != null ? Integer.valueOf(String.valueOf(paramMap.get("current"))) : 1;
        Integer pageSize = paramMap.get("pageSize") != null ? Integer.valueOf(String.valueOf(paramMap.get("pageSize"))) : 10;
        // 查询项目药品数据
        StringBuilder sql = new StringBuilder("SELECT id, medicine_id, unit_price, create_username, create_time, update_username, update_time FROM project_medicine WHERE project_id = ? LIMIT ?, ?");
        List<Map<String, Object>> projectDrugs = executeSQL(sql.toString(), projectId, (current - 1) * pageSize, pageSize);
        // 查询总条数
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) AS total FROM project_medicine WHERE project_id = ?");
        List<Map<String, Object>> totalResult = executeSQL(countSql.toString(), projectId);
        Integer total = Integer.valueOf(String.valueOf(totalResult.get(0).get("total")));
        Map<String, Object> result = new HashMap<>();
        result.put("data", projectDrugs);
        result.put("total", total);
        return new Result(true, "查询成功", result);
    }

    public Result addOperationalData(Map<String, Object> paramMap) {
        String date = paramMap.get("date") != null ? String.valueOf(paramMap.get("date")) : null;
        Integer projectId = paramMap.get("projectId") != null ? Integer.valueOf(String.valueOf(paramMap.get("projectId"))) : null;
        String drugConsumption = paramMap.get("drugConsumption") != null && !String.valueOf(paramMap.get("drugConsumption")).isEmpty() ? String.valueOf(paramMap.get("drugConsumption")) : null;
        String electricityConsumption = paramMap.get("electricityConsumption") != null ? String.valueOf(paramMap.get("electricityConsumption")) : null;
        String waterConsumption = paramMap.get("waterConsumption") != null ? String.valueOf(paramMap.get("waterConsumption")) : null;
        String reporter = paramMap.get("reporter") != null ? String.valueOf(paramMap.get("reporter")) : null;
        String reportTime = paramMap.get("reportTime") != null ? String.valueOf(paramMap.get("reportTime")) : null;
        // 新增运营数据
        StringBuilder sql = new StringBuilder("INSERT INTO operational_data (date, project_id, drug_consumption, electricity_consumption, water_consumption, reporter, report_time) VALUES (?, ?, ?, ?, ?, ?, ?)");
        int rows = executeSQL(sql.toString(), date, projectId, drugConsumption, electricityConsumption, waterConsumption, reporter, reportTime);
        if (rows > 0) {
            return new Result(true, "新增成功", null);
        } else {
            return new Result(false, "新增失败", null);
        }
    }

    public Result getDrugUsage(Map<String, Object> paramMap) {
        // 获取参数
        Integer projectId = paramMap.get("projectId") != null ? Integer.valueOf(String.valueOf(paramMap.get("projectId"))) : null;
        Integer medicineId = paramMap.get("medicineId") != null ? Integer.valueOf(String.valueOf(paramMap.get("medicineId"))) : null;
        // 检查参数是否为空
        if (projectId == null || medicineId == null) {
            return new Result(false, "项目ID和药品ID不能为空", null);
        }
        // 构建查询SQL
        StringBuilder sql = new StringBuilder("SELECT usage_quantity FROM project_medicine_usage WHERE project_id = ? AND medicine_id = ?");
        List<Object> params = new ArrayList<>();
        params.add(projectId);
        params.add(medicineId);
        // 执行查询
        List<Map<String, Object>> result = executeSQL(sql.toString(), params.toArray());
        // 检查查询结果
        if (result == null || result.isEmpty()) {
            return new Result(false, "未找到该项目中该药品的使用数量", null);
        }
        // 获取使用数量
        Integer usageQuantity = result.get(0).get("usage_quantity") != null ? Integer.valueOf(String.valueOf(result.get(0).get("usage_quantity"))) : null;
        // 返回结果
        Map<String, Object> data = new HashMap<>();
        data.put("usageQuantity", usageQuantity);
        return new Result(true, "查询成功", data);
    }

    public Result getProjectDrugsDNI(Map<String, Object> paramMap) {
        // 获取参数
        Integer projectId = paramMap.get("projectId") != null ? Integer.valueOf(String.valueOf(paramMap.get("projectId"))) : null;
        Integer current = paramMap.get("current") != null ? Integer.valueOf(String.valueOf(paramMap.get("current"))) : 1;
        Integer pageSize = paramMap.get("pageSize") != null ? Integer.valueOf(String.valueOf(paramMap.get("pageSize"))) : 10;
        // 检查projectId是否为空
        if (projectId == null) {
            return new Result(false, "项目ID不能为空", null);
        }
        // 构建查询药品信息的SQL
        StringBuilder sql = new StringBuilder("SELECT pmu.id, m.medicine_name AS drugName, pmu.unit_price AS unitprice, pmu.usage_quantity AS quantityused ");
        sql.append("FROM project_medicine_usage pmu ");
        sql.append("JOIN medicine m ON pmu.medicine_id = m.id ");
        sql.append("WHERE pmu.project_id = ? ");
        sql.append("LIMIT ?, ?");
        // 构建查询总条数的SQL
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) AS total ");
        countSql.append("FROM project_medicine_usage pmu ");
        sql.append("JOIN medicine m ON pmu.medicine_id = m.id ");
        sql.append("WHERE pmu.project_id = ?");
        // 执行查询总条数的SQL
        List<Map<String, Object>> countResult = executeSQL(countSql.toString(), projectId);
        Integer total = countResult.get(0) != null ? Integer.valueOf(String.valueOf(countResult.get(0).get("total"))) : 0;
        // 计算分页偏移量
        int offset = (current - 1) * pageSize;
        // 执行查询药品信息的SQL
        List<Map<String, Object>> data = executeSQL(sql.toString(), projectId, offset, pageSize);
        // 构建返回结果
        Map<String, Object> resultData = new HashMap<>();
        resultData.put("total", total);
        resultData.put("data", data);
        return new Result(true, "查询成功", resultData);
    }

    public Result getAllProjectNames6HA(Map<String, Object> paramMap) {
        // 构建查询所有项目名称的SQL
        StringBuilder sql = new StringBuilder("SELECT id, project_name FROM project");
        // 执行SQL查询
        List<Map<String, Object>> result = executeSQL(sql.toString());
        // 构建返回结果
        Result response = new Result();
        response.setFlag(true);
        response.setMessage("查询成功");
        response.setData(result);
        return response;
    }
}
